/**
 * Course数据模型模块
 */

//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");

class CourseDB {
    /**
     * @constructor
     * @private
     */
    constructor() {
        this.dbFile = config.dbFile;
        this.instance = null;
        this.db = null;
    }

    /**
     * 创建CourseDB实例对象
     * @returns {CourseDB} CourseDB实例
     */
    static getInstance() {
        if (!this.instance) {
            this.instance = new CourseDB();
        }
        return this.instance;
    }

    /**
     * 连接数据库
     * @returns {Promise}
     */
    connect() {
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(this.dbFile, (err) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve("connect ok.");
                }
            });
        });
    }
    /**
     * 关闭数据库
     * @returns  {Promise}
     */
    close() {
        return new Promise((resolve, reject) => {
            this.db.close((err) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve("close ok.");
                }
            });
        });
    }

    /**
 * 新增课程
 * @param {Object} course 课程数据
 * @returns {Promise}
 */
    add(course) {
        return new Promise((resolve, reject) => {
            let sql = `INSERT INTO courses (
            teacher_id, name, subject, category, price, difficulty, role, des, avatar, 
            created_time, updated_time, status, average_rating, rating_count
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`;

            let params = [
                course.teacherId,
                course.name,
                course.subject,
                course.category,
                course.price,
                course.difficulty,
                course.role,
                course.des,
                course.avatar,
                course.createdTime,
                course.updatedTime,
                course.status,
                course.averageRating,
                course.ratingCount,
            ];

            this.db.run(sql, params, function (err) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.lastID); // 插入的数据的自增ID
                }
            });
        });
    }

    /**
* 删除课程
* @param {Number} courseId 课程ID
* @returns  {Promise}
*/
    remove(courseId) {
        return new Promise((resolve, reject) => {
            let sql = "DELETE FROM courses WHERE id = ?";
            let params = [courseId];
            this.db.run(sql, params, function (err, result) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.changes); //影响的记录数
                }
            });
        });
    }

    /**
 * 修改课程信息
 * @param {Object} course 课程数据
 * @returns  {Promise}
 */
    update(course) {
        return new Promise((resolve, reject) => {
            let sql = `UPDATE courses SET
            name = ?, price = ?, role = ?, des = ?, avatar = ?, status = ?
            WHERE id = ?;`;
            let params = [
                course.name,
                course.price,
                course.role,
                course.des,
                course.avatar,
                course.status,
                course.id,
            ];
            this.db.run(sql, params, function (err, result) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.changes); //影响的记录数
                }
            });
        });
    }

    /**
     * 获取指定ID的课程信息
     * @param {Number} courseId 课程ID
     * @returns  {Promise}
     */
    find(courseId) {
        return new Promise((resolve, reject) => {
            let sql = "SELECT courses.*, teachers.username, teachers.email FROM courses INNER JOIN teachers ON courses.teacher_id = teachers.id WHERE courses.id = ?;";
            let params = [courseId];
            this.db.get(sql, params, (err, result) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    }

    /**
 * 获取课程信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns  {Promise}
 */
    findAll(limit = -1, offset = -1,orderBy = "id", sort = "DESC") {
        return new Promise((resolve, reject) => {
            let sql = "";
            let params = [];
            if (limit === -1) {
                sql = `SELECT * FROM courses ORDER BY ${orderBy} ${sort}`;
            } else if (offset === -1) {
                sql = `SELECT * FROM courses ORDER BY ${orderBy} ${sort} LIMIT ?`;
                params[0] = limit;
            } else {
                sql = `SELECT * FROM courses ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
                params[0] = limit;
                params[1] = offset;
            }
            this.db.all(sql, params, (err, result) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    }

    /**
* 获取课程数量
* @returns  {Promise}
*/
    getCount() {
        return new Promise((resolve, reject) => {
            let sql = "SELECT count(1) AS total FROM courses";
            let params = [];
            this.db.get(sql, params, function (err, result) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    }

/**
* 搜索课程(按课程名/课程学科体系/课程分类)
* @param {String} q 查询关键字
* @param {Number} limit 数量
* @param {Number} offset 偏移
* @returns {Promise} 
*/
    search(q, limit = -1, offset = -1) {
        return new Promise((resolve, reject) => {
            let sql = "";// 初始化SQL语句为空字符串
            q = `%${q}%`;// 对查询参数进行模糊匹配处理
            let params = [q, q, q];// 初始化查询参数数组

            if (limit === -1) {// 如果limit为-1，即不限制查询结果数量
                // 构建SQL语句，查询name、subject或category字段匹配查询参数的记录
                sql = "SELECT * FROM courses WHERE name LIKE ? OR subject LIKE ? OR category = ?";
            } else if (offset === -1) {// 如果offset为-1，即不指定查询结果的偏移量
                // 构建SQL语句，查询name、subject或category字段匹配查询参数的记录，并限制查询结果数量为limit
                sql = "SELECT * FROM courses WHERE name LIKE ? OR subject LIKE ? OR category = ? LIMIT ?";
                params.push(limit);// 将limit添加到查询参数数组中
            } else {// 如果limit和offset都不为-1，即指定查询结果数量和偏移量
                // 构建SQL语句，查询name、subject或category字段匹配查询参数的记录，并限制查询结果数量为limit，从offset位置开始
                sql = "SELECT * FROM courses WHERE name LIKE ? OR subject LIKE ? OR category = ? LIMIT ? OFFSET ?";          
                params.push(limit);// 将limit添加到查询参数数组中
                params.push(offset);// 将offset添加到查询参数数组中
            }
            this.db.all(sql, params, (err, result) => {// 执行数据库查询操作
                if (err) {
                    // 如果查询出错，则输出错误信息并调用reject方法返回错误
                    util.err(err);
                    reject(err);
                } else {
                    // 如果查询成功，则调用resolve方法返回查询结果
                    resolve(result);
                }
            });
        });
    }

/**
 * 获取指定教师ID的评论信息
 * @param {Number} teacherId 教师ID
 * @returns  {Promise}
 */
    findAllByTeacherId(teacherId) {
        return new Promise((resolve, reject) => {
            // 定义SQL查询语句
            let sql = `SELECT 
            c.id AS course_id,
            c.name AS course_name,
            c.subject AS course_subject,
            c.category AS course_category,
            c.price AS course_price,
            c.difficulty AS course_difficulty,
            c.des AS course_des,
            c.created_time AS created_time,
            c.updated_time AS updated_time,
            t.id AS teacher_id,
            t.username AS teacher_username,
            t.phone_number AS teacher_phone,
            t.email AS teacher_email
        FROM courses AS c
        INNER JOIN teachers AS t ON c.teacher_id = t.id
        WHERE t.id = ?
            `;
            let params = [teacherId]; // 定义查询参数
            this.db.get(sql, params, (err, result) => {// 执行查询
                if (err) {// 如果查询出错，输出错误信息并拒绝Promise
                    util.err(err);
                    reject(err);
                } else {// 如果查询成功，将结果解析为Promise
                    resolve(result);
                }
            });
        });
    }


}

module.exports = CourseDB;